﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;


namespace Trade.DAL
{
    public class Product
    {
        public const string SQL_SELECT_PRODUCT = "Select P.ProductNo, P.ProductName, P.ShortName, P.ProductSpec, P.ProductModel, P.ProdBarcode, P.ProdClass, S0.SubjectName as ProdClassDes, P.BaseUnit, S1.SubjectName as BaseUnitDes, P.AidUnit, S2.SubjectName as AidUnitDes, P.BaseForAid, P.Place, P.Brand, P.Bopomofo, P.Remark, P.RecordId from Product P left join SubjectMessage S0 on P.ProdClass = S0.SubjectId left join SubjectMessage S1 on P.BaseUnit = S1.SubjectId left join SubjectMessage S2 on P.AidUnit = S2.SubjectId";
        public const string SQL_WHERE = " where 1 > 2";
        public const string SQL_WHERE_RECORDID = " where P.RecordId = @RecordId";

        //读取所有的商品资料列表到DataTable中
        public DataTable GetProductList()
        {
            return SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_PRODUCT, null);
        }

        //创建数据表结构
        public DataTable ProductTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_PRODUCT + SQL_WHERE, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的记录编号读取相应的商品资料
        public DataRow GetProductByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DataTable dt = SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_PRODUCT + SQL_WHERE_RECORDID, parm);
            DataRow dr = dt.Rows[0];
            return dr;
        }

        public ProductInfo GetProductInfoByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            ProductInfo productInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_PRODUCT + SQL_WHERE_RECORDID, parm))
            {
                while (dr.Read())
                {
                    productInfo = new ProductInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetInt32(6), dr.GetInt32(8),
                                                                dr.GetInt32(10), dr.GetInt32(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetInt32(17));
                }
            }
            return productInfo;
        }

        //检测商品编号的唯一性
        public bool IsUniqueProductNo(string productNo, int recordId)
        {
            string sqlSelect = "Select count(ProductNo) from Product where ltrim(rtrim(ProductNo)) = @ProductNo and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@ProductNo", SqlDbType.VarChar, 8),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = productNo.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //检测指定的商品编号是否已定义
        public bool IsExistProductNo(string productNo)
        {
            string sqlSelect = "Select count(ProductNo) from Product where ltrim(rtrim(ProductNo)) = @ProductNo";
            SqlParameter parm = new SqlParameter("@ProductNo", SqlDbType.VarChar, 8);
            parm.Value = productNo.Trim();

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parm) == 0 ? true : false;
        }

        //将数据更新到数据库
        public void WriteToDatabase(string action, ProductInfo product, string userId, out int recordId)
        {
            string sql = "Exec Usp_ModifyProduct @p_Action, @p_ProductNo, @p_ProductName, @p_ShortName, @p_ProductSpec, @p_ProductModel, @p_ProdBarcode, @p_ProdClass, @p_BaseUnit, @p_AidUnit, @p_BaseForAid, @p_Place, @p_Brand, @p_Bopomofo, @p_Remark, @p_RecordId out, @p_UserId, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                    new SqlParameter("@p_Action", SqlDbType.Char, 1),
                    new SqlParameter("@p_ProductNo", SqlDbType.VarChar, 10),
                    new SqlParameter("@p_ProductName", SqlDbType.VarChar, 120),
                    new SqlParameter("@p_ShortName", SqlDbType.VarChar, 30), 
                    new SqlParameter("@p_ProductSpec", SqlDbType.VarChar, 60),
                    new SqlParameter("@p_ProductModel", SqlDbType.VarChar, 60),
                    new SqlParameter("@p_ProdBarcode", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_ProdClass", SqlDbType.Int),
                    new SqlParameter("@p_BaseUnit", SqlDbType.Int),
                    new SqlParameter("@p_AidUnit", SqlDbType.Int),
                    new SqlParameter("@p_BaseForUnit", SqlDbType.Int),
                    new SqlParameter("@p_Place", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_Brand", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_Bopomofo", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_Remark", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RecordId", SqlDbType.Int),
                    new SqlParameter("@p_UserId", SqlDbType.VarChar, 60),
                    new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = product.ProductNo.Trim();
            parms[2].Value = product.ProductName.Trim();
            parms[3].Value = product.ShortName.Trim();
            parms[4].Value = product.ProductSpec.Trim();
            parms[5].Value = product.ProductModel.Trim();
            parms[6].Value = product.ProdBarcode.Trim();
            parms[7].Value = product.ProdClass;
            parms[8].Value = product.BaseUnit;
            parms[9].Value = product.AidUnit;
            parms[10].Value = product.BaseForAid;
            parms[11].Value = product.Place.Trim();
            parms[12].Value = product.Brand.Trim();
            parms[13].Value = product.Bopomofo.Trim();
            parms[14].Value = product.Remark.Trim();
            parms[15].Value = product.RecordId;
            parms[15].Direction = ParameterDirection.InputOutput;
            parms[16].Value = userId;
            parms[17].Value = "";
            parms[17].Direction = ParameterDirection.InputOutput;
            parms[18].Value = 0;
            parms[18].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
            recordId = (int)parms[15].Value;
        }
    }
}
